package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.StudyFine;

public class StudyFineDao {

	public StudyFineDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";
	 //String pass = "webclass";

	// 참여율에 비례해 돈받기
	public StudyFine getStudyFineInfo(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StudyFine studyFineInfo = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select * from studyFine where sid=? and uid=?");
			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int studyId = rs.getInt("sid");
				int userId = rs.getInt("uid");
				int conductQuest = rs.getInt("conductQuest");
				int myTotalQuest = rs.getInt("myTotalQuest");
				studyFineInfo = new StudyFine(studyId, userId, conductQuest,
						myTotalQuest);
			}

			return studyFineInfo;

		} catch (Exception ex) {
			ex.printStackTrace();
			return studyFineInfo;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 참여율에 비례해 돈받기
	public float getReturnByTotalQuest(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		float participationRate = 0.0f;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select conductQuest, myTotalQuest from studyFine where sid=? and uid=?");
			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				int conductQuest = rs.getInt("conductQuest");
				int myTotalQuest = rs.getInt("myTotalQuest");
				
				participationRate = myTotalQuest != 0 ? (float) conductQuest / myTotalQuest:0;
			}

			return participationRate;

		} catch (Exception ex) {
			ex.printStackTrace();
			return participationRate;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 자기가 수행한 퀘스트 갯수
	public int getReturnByConductQuest(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int conductQuest = 0;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select conductQuest from studyFine where sid=? and uid=?");
			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				conductQuest = rs.getInt("conductQuest");
			}

			return conductQuest;

		} catch (Exception ex) {
			ex.printStackTrace();
			return conductQuest;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// study에 참가될때 자동으로 추가
	public void insertStudyFineInfo(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into studyFine(sid, uid, conductQuest, myTotalQuest) values(?,?,0,0)");
			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// totalQuest update
	public void updateTotalQuest(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update studyfine set myTotalQuest=myTotalQuest+1 where sid=? and uid=?");
			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// totalQuest update
	public void updateConductQuest(int sid, int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("update studyfine set conductQuest=conductQuest+1 where sid=? and uid=?");
			pstmt.setInt(1, sid);
			pstmt.setInt(2, uid);
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

}